import sys
import re
import sqlite3

START = re.compile(r'^\s+<tr>\s*$')
END   = re.compile(r'</tr>')
ENTRY = re.compile(r'<td[^>]+>(.*)</td>')

record = []

db = sqlite3.connect("ontario_salary.sqlite")
c = db.cursor()
c.execute("""
  create table if not exists T (
    school STRING,
    name   STRING,
    position STRING,
    salary FLOAT,
    deduction  FLOAT
  )
""")
db.commit()

query = "insert into T values(?, ?, ?, ?, ?)"

def currency(string):
  x = string.replace('$', '').replace(',', '')
  return float(x)

with open("ontario_salary.txt") as f:
  for line in f.readlines():
    if START.search(line):
      record = []
    elif END.search(line) and record:
      school, last, first, position, salary, deduction  = record
      salary = currency(salary)
      deduction = currency(deduction)
      name = "%s %s" % (first, last)
      c.execute(query, [school, name, position, salary, deduction])
    else:
      m = ENTRY.search(line)
      if m: record.append(m.group(1))

db.commit()
print "All done."
